/*
 Navicat Premium Data Transfer

 Source Server         : 阿里云
 Source Server Type    : MySQL
 Source Server Version : 80022
 Source Host           : 47.98.151.35:3306
 Source Schema         : trainbook

 Target Server Type    : MySQL
 Target Server Version : 80022
 File Encoding         : 65001

 Date: 25/12/2020 01:50:21
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for seat
-- ----------------------------
DROP TABLE IF EXISTS `seat`;
CREATE TABLE `seat` (
  `id` int NOT NULL AUTO_INCREMENT,
  `level` int DEFAULT NULL COMMENT '座位等级',
  `trainId` int DEFAULT NULL COMMENT '有关车次',
  `all_num` int DEFAULT NULL COMMENT '共计座位',
  `last_num` int DEFAULT NULL COMMENT '剩余座位',
  PRIMARY KEY (`id`),
  KEY `level` (`level`),
  KEY `train` (`trainId`),
  CONSTRAINT `level` FOREIGN KEY (`level`) REFERENCES `seatItem` (`seat_level`),
  CONSTRAINT `train` FOREIGN KEY (`trainId`) REFERENCES `train` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='车次座位表';

-- ----------------------------
-- Records of seat
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for seatItem
-- ----------------------------
DROP TABLE IF EXISTS `seatItem`;
CREATE TABLE `seatItem` (
  `seat_level` int DEFAULT NULL COMMENT '座位等级索引值',
  `seat_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '座位等级',
  `max_num` int DEFAULT NULL,
  KEY `seat` (`seat_level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='座位名映射表';

-- ----------------------------
-- Records of seatItem
-- ----------------------------
BEGIN;
INSERT INTO `seatItem` VALUES (1, '无座', 100);
INSERT INTO `seatItem` VALUES (2, '特等座', 30);
INSERT INTO `seatItem` VALUES (3, '一等座', 100);
INSERT INTO `seatItem` VALUES (4, '二等座', 200);
INSERT INTO `seatItem` VALUES (5, '软卧', 150);
INSERT INTO `seatItem` VALUES (6, '硬卧', 300);
COMMIT;

-- ----------------------------
-- Table structure for site
-- ----------------------------
DROP TABLE IF EXISTS `site`;
CREATE TABLE `site` (
  `id` int NOT NULL AUTO_INCREMENT,
  `siteName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '站点名',
  `longitude` double(255,0) NOT NULL COMMENT '经度',
  `latitude` double(255,0) NOT NULL COMMENT '纬度',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='站点表';

-- ----------------------------
-- Records of site
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for site2site
-- ----------------------------
DROP TABLE IF EXISTS `site2site`;
CREATE TABLE `site2site` (
  `id` int NOT NULL AUTO_INCREMENT,
  `siteA` int NOT NULL COMMENT '站点 A',
  `siteB` int NOT NULL COMMENT '站点 B',
  `distance` double NOT NULL COMMENT '站点间距离',
  `requiredTime` bigint NOT NULL COMMENT '所需时间',
  `price` double(10,2) NOT NULL COMMENT '票价',
  PRIMARY KEY (`id`),
  KEY `s2sA` (`siteA`),
  KEY `s2sB` (`siteB`),
  CONSTRAINT `s2sA` FOREIGN KEY (`siteA`) REFERENCES `site` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `s2sB` FOREIGN KEY (`siteB`) REFERENCES `site` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='站点与站点间相连的表';

-- ----------------------------
-- Records of site2site
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for train
-- ----------------------------
DROP TABLE IF EXISTS `train`;
CREATE TABLE `train` (
  `id` int NOT NULL AUTO_INCREMENT,
  `trainName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `startTime` datetime NOT NULL COMMENT '开始时间',
  `endTime` datetime NOT NULL COMMENT '结束时间',
  PRIMARY KEY (`id`),
  CONSTRAINT `trainSites` FOREIGN KEY (`id`) REFERENCES `trainSites` (`trainId`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='车次表';

-- ----------------------------
-- Records of train
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for trainSites
-- ----------------------------
DROP TABLE IF EXISTS `trainSites`;
CREATE TABLE `trainSites` (
  `id` int NOT NULL AUTO_INCREMENT,
  `siteId` int NOT NULL COMMENT '站点id',
  `trainId` int DEFAULT NULL,
  `order` int NOT NULL COMMENT '顺序（起始站点为 0）',
  `arriveTime` datetime NOT NULL COMMENT '到站时间',
  `leaveTime` datetime NOT NULL COMMENT '出发时间',
  `waitTime` bigint NOT NULL COMMENT '停留时间',
  PRIMARY KEY (`id`),
  KEY `siteId` (`siteId`),
  KEY `trainId` (`trainId`),
  CONSTRAINT `site` FOREIGN KEY (`siteId`) REFERENCES `site` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='车次途径站点表';

-- ----------------------------
-- Records of trainSites
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `userName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户名',
  `passWord` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '密码',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表';

-- ----------------------------
-- Records of user
-- ----------------------------
BEGIN;
INSERT INTO `user` VALUES (1, '11', '22');
INSERT INTO `user` VALUES (2, 'ycx', 'ycx123');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;
